#!/usr/bin/env python
# -*- coding: utf-8 -*-
__title__ = '配置文档'
__author__ = 'HaiFeng'
__mtime__ = '20180821'

import sys, os
from color_log import Logger
from redis import Redis
# import pymysql
# import MySQLdb
from pandas import DataFrame, read_sql_query, read_sql_table, read_sql
from sqlalchemy import create_engine

log = Logger()
zmq_port = 5055

# ctp前置配置
front_quote = 'tcp://180.166.132.68:41213'
broker = '6000'
investor = '111'
pwd = ''
appid = ''
authcode = ''

redis_login = {
    'host': 'r-uf67z1pctm8ma19qww.redis.rds.aliyuncs.com',
    'port': 6379,
    'decode_responses': True,
}
rds: Redis = Redis(**redis_login)

url_mysql = 'mysql+pymysql://haifeng:Gdqh2018@rm-uf6gwl9bm00b481sy.mysql.rds.aliyuncs.com:3306/future_data?charset=utf8'


# url_ora_settle = 'oracle://user4read:user4read0725@192.168.52.12:1521/CRMS'
# url_oracle_hot= 'oracle://settlement:settlement@172.17.118.81:8221/?service_name=gdqh'
# def oracle_df(sql: str) -> DataFrame:
#     en = create_engine(url_ora_settle, pool_size=20, encoding='utf-8', convert_unicode=True)
#     conn = en.raw_connection()
#     return read_sql_query(sql, conn)


def mysql_fetchall(sql: str) -> list:
    en = create_engine(url_mysql)
    conn = en.raw_connection()
    cur = conn.cursor()
    cur.execute(sql)
    return [r for r in cur.fetchall()]


def mysql_df(sql: str) -> DataFrame:
    en = create_engine(url_mysql)
    conn = en.raw_connection()
    return read_sql(sql, conn)


# def oracle_2_mysql(table_name: str):
#     en = create_engine(url_ora_settle, pool_size=20, encoding='utf-8', convert_unicode=True, echo=True)
#     conn = en.connect()  # .raw_connection()
#     en_mysql = create_engine(url_mysql)
#     conn_mysql = en_mysql.raw_connection()
#     df = read_sql_table(table_name, con=conn, schema='SOURCETMP')
#     en_mysql.execute('TRUNCATE `future_data`.`t_instrument`')
#     # conn_mysql.execute('TRUNCATE `future_data`.`t_instrument` ')
#     df.to_sql(table_name, en_mysql, schema='future_data', if_exists='append', index=False)

# 取合约信息
sql_instruments = "select INSTRUMENTID as InstrumentID, PRODUCTID as ProductID, PRICETICK as PriceTick, VOLUMEMULTIPLE as VolumeMultiple, EXCHANGEID as ExchangeID, case PRODUCTCLASS when '1' then 'Future' when '2' then 'Option' else 'other' end  as ProductType, MAXLIMITORDERVOLUME as MaxOrderVolume from future_data.t_instrument where EXPIREDATE > date_format(sysdate(), '%Y%m%d')"

#######  兼容查询
# 品种交易时间
sql_time = 'select GroupId, WorkingTimes, max(OpenDate) from future_data.tradingtime group by GroupId'
# 取交易日历
sql_trade_date = 'select day from future_data.trade_date where istrading = 1'
# 分钟K线 ** 有参数时需要用%% 解决预编译的问题, 否则会提示 unsupported format character 'Y'
sql_min = "select date_format(`DateTime`, '%Y-%m-%d %H:%i:%s') as _id, Instrument, TradingDay, High, Low, `Open`, `Close`, Volume, OpenInterest from future_data.future_min where Instrument = '{}' and TradingDay in ({})"
sql_product = 'SELECT `PRODUCTID` as _id, `PRICETICK` as "PriceTick", `VOLUMEMULTIPLE` as "VolumeTuple", `EXCHANGEID` as "ExchangeID", `PRODUCTCLASS` as "ProductType", `MAXLIMITORDERVOLUME`, max(`EXPIREDATE`) FROM `future_data`.`t_instrument` GROUP BY `PRODUCTID` '
sql_inst_proc = """
select INSTRUMENTID as _id, PRODUCTID as ProductID  from future_data.t_instrument where EXPIREDATE > date_format(sysdate(), '%Y%m%d')
UNION 
SELECT concat(`PRODUCTID`, '_000'), `PRODUCTID`  from future_data.t_instrument where EXPIREDATE > date_format(sysdate(), '%Y%m%d') GROUP BY `PRODUCTID` 
"""
sql_rate = 'select `InstrumentID`, `Rate` from future_data.rate_000'
sql_888 = 'SELECT `ProductID` as _id, `InstrumentID` as value, MAX(`rate`) as Rate FROM `rate_000` GROUP BY `ProductID` '
